package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import pojo.Customer;
import pojo.Operator;

import common.PinYinUtils;
import common.RowMapper;
import common.SQLTemplate;

import dao.CustomerDAO;

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {

	@Override
	public List<Customer> getCustomersByNameAndOperatorId(String customerName,
			String operatorId) {
		String sql = "";
		if (operatorId.equalsIgnoreCase("ADMIN")) {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "ADN T_CUSTOMER.NAME LIKE%'" + customerName + "%';";
		} else {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND T_CUSTOMER.NAME LIKE'%" + customerName + "%' "
					+ "AND OPERATOR_ID='" + operatorId + "';";
		}
		List<Customer> customers = null;
		try {
			customers = (List<Customer>) queryForList(sql,
					new CustomerRowMapper());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	public List<Customer> getCustomersByMobilePhoneAndOperatorId(
			String phoneNum, String operatorId) {
		String sql = "";
		if (operatorId.equalsIgnoreCase("ADMIN")) {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND T_CUSTOMER.MOBILE_PHONE LIKE%'" + phoneNum + "%';";

		} else {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND T_CUSTOMER.MOBILE_PHONE LIKE'%" + phoneNum + "%' "
					+ "AND OPERATOR_ID='" + operatorId + "';";
		}
		List<Customer> customers = null;
		try {
			customers = (List<Customer>) queryForList(sql,
					new CustomerRowMapper());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	public List<Customer> getCustomersByIdentityCardAndOperatorId(
			String identityCard, String operatorId) {
		String sql = "";
		if (operatorId.equalsIgnoreCase("ADMIN")) {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND T_CUSTOMER.IDENTITY_CARD LIKE%'" + identityCard
					+ "%';";

		} else {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND T_CUSTOMER.IDENTITY_CARD LIKE'%" + identityCard
					+ "%' " + "AND OPERATOR_ID='" + operatorId + "';";
		}
		List<Customer> customers = null;
		try {
			customers = (List<Customer>) queryForList(sql,
					new CustomerRowMapper());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	@Override
	public List<Customer> getCustomersByOperatorId(String operatorId) {
		String sql = "";
		if (operatorId.equalsIgnoreCase("ADMIN")) {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL + ";";
		} else {
			sql = SQLTemplate.CUSTOMER_SELECT_JOIN_CUSTOMER_TYPE_OPERATOR_SQL
					+ "AND OPERATOR_ID='" + operatorId + "';";
		}
		List<Customer> customers = null;
		try {
			customers = (List<Customer>) queryForList(sql,
					new CustomerRowMapper());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	@Override
	public Customer add(Customer customer) {
		// 设置CustomerName的拼音开头 方便查询
		String oldName = customer.getName();
		customer.setName(oldName + "["
				+ PinYinUtils.getCapitalizePinYin(oldName) + "]");
		String tableName = "T_CUSTOMER";
		String[] columnNames = { "NAME", "SEX", "AGE", "IDENTITY_CARD",
				"MOBILE_PHONE", "TELEPHONE", "ADDRESS", "CUSTOMER_TYPE_ID",
				"OPERATOR_ID" };
		String[] values = { customer.getName(), customer.getSex(),
				customer.getAge(), customer.getIdentityCard(),
				customer.getMobilePhone(), customer.getTelephone(),
				customer.getAddress(), customer.getCustomerTypeId(),
				customer.getOperatorId() };
		String sql = SQLTemplate.addSQLTemplate(tableName, columnNames, values);
		try {
			insert(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customer;
	}

	@Override
	public Customer update(Customer customer) {
		String oldName = customer.getName();
		oldName = oldName.replaceAll("\\[\\w+\\]", "");
		customer.setName(oldName + "["
				+ PinYinUtils.getCapitalizePinYin(oldName) + "]");
		String tableName = "T_CUSTOMER";
		String[] columnNames = { "NAME", "SEX", "AGE", "IDENTITY_CARD",
				"MOBILE_PHONE", "TELEPHONE", "ADDRESS", "CUSTOMER_TYPE_ID",
				"OPERATOR_ID" };
		String[] values = { customer.getName(), customer.getSex(),
				customer.getAge(), customer.getIdentityCard(),
				customer.getMobilePhone(), customer.getTelephone(),
				customer.getAddress(), customer.getCustomerTypeId(),
				customer.getOperatorId() };
		String sql = SQLTemplate.updateSQLTemplate(tableName, columnNames,
				values) + "WHERE ID='" + customer.getId() + "';";
		try {
			update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customer;
	}

	@Override
	public boolean deleteById(String id) {
		boolean state = false;
		String sql = SQLTemplate.deleteSQLTemplate("T_CUSTOMER") + "WHERE ID='"
				+ id + "'";
		try {
			delete(sql);
			state = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return state;
	}

	@Override
	public int getCustomersSize() {
		int r = 0;
		try {
			r = getTotalRow(SQLTemplate.countSizeSQLTemplate("T_CUSTOMER"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return r;
	}

	private class CustomerRowMapper implements RowMapper<Object> {
		private CustomerRowMapper() {
		}

		public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
			String id = rs.getString("id");
			String name = rs.getString("name");
			String sex = rs.getString("sex");
			String age = rs.getString("age");
			String identityCard = rs.getString("identity_card");
			String mobilePhone = rs.getString("mobile_phone");
			String telephone = rs.getString("telephone");
			String address = rs.getString("address");
			String operatorId = rs.getString("operator_id");
			String operatorName = rs.getString("operator_name");
			String customerTypeId = rs.getString("customer_type_id");
			String customerType = rs.getString("customer_type");
			Customer customer = new Customer();
			if (id != null) {
				customer.setId(id);
			}
			if (name != null) {
				name = name.replaceAll("\\[\\w+\\]", "");
				customer.setName(name);
			}
			if (sex != null) {
				customer.setSex(sex);
			}
			if (age != null) {
				customer.setAge(age);
			}
			if (identityCard != null) {
				customer.setIdentityCard(identityCard);
			}
			if (mobilePhone != null) {
				customer.setMobilePhone(mobilePhone);
			}
			if (telephone != null) {
				customer.setTelephone(telephone);
			}
			if (address != null) {
				customer.setAddress(address);
			}
			if (operatorId != null) {
				customer.setOperatorId(operatorId);
			}
			if (operatorName != null) {
				customer.setOperatorName(operatorName);
			}
			if (customerTypeId != null) {
				customer.setCustomerTypeId(customerTypeId);
			}
			if (customerType != null) {
				customer.setCustomerType(customerType);
			}
			return customer;
		}
	}
}